{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "b1ef7306",
   "metadata": {},
   "source": [
    "# 不一致数据的处理"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "5bbb8913",
   "metadata": {},
   "outputs": [],
   "source": [
    "##keep\n",
    "# pip3 install python-Levenshtein thefuzz\n",
    "import pandas as pd\n",
    "from thefuzz import process\n",
    "from thefuzz import fuzz"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "947eb100",
   "metadata": {},
   "source": [
    "## 导入数据"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "a674c58f",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Unnamed: 0</th>\n",
       "      <th>S#</th>\n",
       "      <th>Date</th>\n",
       "      <th>City</th>\n",
       "      <th>Latitude</th>\n",
       "      <th>Longitude</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>Sunday-November 19-1995</td>\n",
       "      <td>Islamabad</td>\n",
       "      <td>33.7180</td>\n",
       "      <td>73.0718</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>Monday-November 6-2000</td>\n",
       "      <td>Karachi</td>\n",
       "      <td>24.9918</td>\n",
       "      <td>66.9911</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2</td>\n",
       "      <td>3</td>\n",
       "      <td>Wednesday-May 8-2002</td>\n",
       "      <td>Karachi</td>\n",
       "      <td>24.9918</td>\n",
       "      <td>66.9911</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>3</td>\n",
       "      <td>4</td>\n",
       "      <td>Friday-June 14-2002</td>\n",
       "      <td>Karachi</td>\n",
       "      <td>24.9918</td>\n",
       "      <td>66.9911</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>4</td>\n",
       "      <td>5</td>\n",
       "      <td>Friday-July 4-2003</td>\n",
       "      <td>Quetta</td>\n",
       "      <td>30.2095</td>\n",
       "      <td>67.0182</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   Unnamed: 0  S#                     Date       City  Latitude Longitude\n",
       "0           0   1  Sunday-November 19-1995  Islamabad   33.7180   73.0718\n",
       "1           1   2   Monday-November 6-2000    Karachi   24.9918   66.9911\n",
       "2           2   3     Wednesday-May 8-2002   Karachi    24.9918   66.9911\n",
       "3           3   4      Friday-June 14-2002    Karachi   24.9918   66.9911\n",
       "4           4   5       Friday-July 4-2003     Quetta   30.2095   67.0182"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "##keep\n",
    "df = pd.read_csv('./datasets/pakistan_cities.csv')\n",
    "df.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "b1c852ed",
   "metadata": {},
   "source": [
    "## 查看City列的数据\n",
    "用unique查看不同的City，对City按名称排序。"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "id": "dc7cf649",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array(['ATTOCK', 'Attock ', 'Bajaur Agency', 'Bannu', 'Bhakkar ', 'Buner',\n",
       "       'Chakwal ', 'Chaman', 'Charsadda', 'Charsadda ', 'D. I Khan',\n",
       "       'D.G Khan', 'D.G Khan ', 'D.I Khan', 'D.I Khan ', 'Dara Adam Khel',\n",
       "       'Dara Adam khel', 'Fateh Jang', 'Ghallanai, Mohmand Agency ',\n",
       "       'Gujrat', 'Hangu', 'Haripur', 'Hayatabad', 'Islamabad',\n",
       "       'Islamabad ', 'Jacobabad', 'KURRAM AGENCY', 'Karachi', 'Karachi ',\n",
       "       'Karak', 'Khanewal', 'Khuzdar', 'Khyber Agency', 'Khyber Agency ',\n",
       "       'Kohat', 'Kohat ', 'Kuram Agency ', 'Lahore', 'Lahore ',\n",
       "       'Lakki Marwat', 'Lakki marwat', 'Lasbela', 'Lower Dir', 'MULTAN',\n",
       "       'Malakand ', 'Mansehra', 'Mardan', 'Mohmand Agency',\n",
       "       'Mohmand Agency ', 'Mohmand agency', 'Mosal Kor, Mohmand Agency',\n",
       "       'Multan', 'Muzaffarabad', 'North Waziristan', 'North waziristan',\n",
       "       'Nowshehra', 'Orakzai Agency', 'Peshawar', 'Peshawar ', 'Pishin',\n",
       "       'Poonch', 'Quetta', 'Quetta ', 'Rawalpindi', 'Sargodha',\n",
       "       'Sehwan town', 'Shabqadar-Charsadda', 'Shangla ', 'Shikarpur',\n",
       "       'Sialkot', 'South Waziristan', 'South waziristan', 'Sudhanoti',\n",
       "       'Sukkur', 'Swabi ', 'Swat', 'Swat ', 'Taftan',\n",
       "       'Tangi, Charsadda District', 'Tank', 'Tank ', 'Taunsa',\n",
       "       'Tirah Valley', 'Totalai', 'Upper Dir', 'Wagah', 'Zhob', 'bannu',\n",
       "       'karachi', 'karachi ', 'lakki marwat', 'peshawar', 'swat'],\n",
       "      dtype=object)"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "cities = df.City.unique()\n",
    "cities.sort()\n",
    "cities"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "ba54e486",
   "metadata": {},
   "source": [
    "## 清除大小写与空格\n",
    "将City全部转换成小写，并清除字符前后的空格，排序后输出。"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "id": "de7575be",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array(['attock', 'bajaur agency', 'bannu', 'bhakkar', 'buner', 'chakwal',\n",
       "       'chaman', 'charsadda', 'd. i khan', 'd.g khan', 'd.i khan',\n",
       "       'dara adam khel', 'fateh jang', 'ghallanai, mohmand agency',\n",
       "       'gujrat', 'hangu', 'haripur', 'hayatabad', 'islamabad',\n",
       "       'jacobabad', 'karachi', 'karak', 'khanewal', 'khuzdar',\n",
       "       'khyber agency', 'kohat', 'kuram agency', 'kurram agency',\n",
       "       'lahore', 'lakki marwat', 'lasbela', 'lower dir', 'malakand',\n",
       "       'mansehra', 'mardan', 'mohmand agency',\n",
       "       'mosal kor, mohmand agency', 'multan', 'muzaffarabad',\n",
       "       'north waziristan', 'nowshehra', 'orakzai agency', 'peshawar',\n",
       "       'pishin', 'poonch', 'quetta', 'rawalpindi', 'sargodha',\n",
       "       'sehwan town', 'shabqadar-charsadda', 'shangla', 'shikarpur',\n",
       "       'sialkot', 'south waziristan', 'sudhanoti', 'sukkur', 'swabi',\n",
       "       'swat', 'taftan', 'tangi, charsadda district', 'tank', 'taunsa',\n",
       "       'tirah valley', 'totalai', 'upper dir', 'wagah', 'zhob'],\n",
       "      dtype=object)"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df['City'] = df['City'].str.strip()\n",
    "df['City'] = df['City'].str.lower()\n",
    "cities = df['City'].unique()\n",
    "cities.sort()\n",
    "cities"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "8b399238",
   "metadata": {},
   "source": [
    "## 使用模糊匹配\n",
    "查找与\"d.i khan\"最匹配的前10个城市名称。"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "id": "73ae2085",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[('d. i khan', 100),\n",
       " ('d.i khan', 100),\n",
       " ('d.g khan', 88),\n",
       " ('khanewal', 50),\n",
       " ('sudhanoti', 47),\n",
       " ('hangu', 46),\n",
       " ('kohat', 46),\n",
       " ('dara adam khel', 45),\n",
       " ('chaman', 43),\n",
       " ('mardan', 43)]"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "matches = process.extract('d.i khan', cities, limit=10, scorer=fuzz.token_sort_ratio)\n",
    "matches"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "39455955",
   "metadata": {},
   "source": [
    "## 将匹配程度较高的字段替换掉\n",
    "匹配ratio大于50分的City字段，可替换成'd.i khan'。"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "id": "887e7e72",
   "metadata": {},
   "outputs": [],
   "source": [
    "close_matches = [m[0] for m in matches if m[1] > 50]\n",
    "df.loc[df.City.isin(close_matches), 'City'] = 'd.i khan'"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "fa748f23",
   "metadata": {},
   "source": [
    "## 验证修改的结果"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "id": "38b89d8e",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "['attock',\n",
       " 'bajaur agency',\n",
       " 'bannu',\n",
       " 'bhakkar',\n",
       " 'buner',\n",
       " 'chakwal',\n",
       " 'chaman',\n",
       " 'charsadda',\n",
       " 'd.i khan',\n",
       " 'dara adam khel',\n",
       " 'fateh jang',\n",
       " 'ghallanai, mohmand agency',\n",
       " 'gujrat',\n",
       " 'hangu',\n",
       " 'haripur',\n",
       " 'hayatabad',\n",
       " 'islamabad',\n",
       " 'jacobabad',\n",
       " 'karachi',\n",
       " 'karak',\n",
       " 'khanewal',\n",
       " 'khuzdar',\n",
       " 'khyber agency',\n",
       " 'kohat',\n",
       " 'kuram agency',\n",
       " 'kurram agency',\n",
       " 'lahore',\n",
       " 'lakki marwat',\n",
       " 'lasbela',\n",
       " 'lower dir',\n",
       " 'malakand',\n",
       " 'mansehra',\n",
       " 'mardan',\n",
       " 'mohmand agency',\n",
       " 'mosal kor, mohmand agency',\n",
       " 'multan',\n",
       " 'muzaffarabad',\n",
       " 'north waziristan',\n",
       " 'nowshehra',\n",
       " 'orakzai agency',\n",
       " 'peshawar',\n",
       " 'pishin',\n",
       " 'poonch',\n",
       " 'quetta',\n",
       " 'rawalpindi',\n",
       " 'sargodha',\n",
       " 'sehwan town',\n",
       " 'shabqadar-charsadda',\n",
       " 'shangla',\n",
       " 'shikarpur',\n",
       " 'sialkot',\n",
       " 'south waziristan',\n",
       " 'sudhanoti',\n",
       " 'sukkur',\n",
       " 'swabi',\n",
       " 'swat',\n",
       " 'taftan',\n",
       " 'tangi, charsadda district',\n",
       " 'tank',\n",
       " 'taunsa',\n",
       " 'tirah valley',\n",
       " 'totalai',\n",
       " 'upper dir',\n",
       " 'wagah',\n",
       " 'zhob']"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "sorted(df.City.unique())"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.10.11"
  },
  "toc": {
   "base_numbering": 1,
   "nav_menu": {},
   "number_sections": true,
   "sideBar": true,
   "skip_h1_title": false,
   "title_cell": "Table of Contents",
   "title_sidebar": "Contents",
   "toc_cell": false,
   "toc_position": {},
   "toc_section_display": true,
   "toc_window_display": false
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}
